import numpy as np
import pandas as pd
import cmath
import math
import os
import shutil
import openpyxl
import datetime

from openpyxl import load_workbook
from datetime import datetime, timedelta

# 方法函数库

def is_nan(value):
    if pd.isnull(value) == True  : 
        reVal = ''
    else:
        reVal = value
    return reVal

def copy_and_rename(src, dst, new_name):
    # 复制文件
    shutil.copy2(src, dst)
    # 获取复制后的文件路径
    copied_file = os.path.join(dst, os.path.basename(src))
    # 修改文件名
    os.rename(copied_file, os.path.join(dst, new_name))

def rename_sheet_openpyxl(filename, sheetname, new_sheetname):
    workbook = openpyxl.load_workbook(filename)
    worksheet = workbook[sheetname]
    worksheet.title = new_sheetname
    workbook.save(filename)


# 执行代码

# 读取Excel文件

# df = pd.read_excel('xbhdl_Data.xlsx')
# xlsx = pd.ExcelFile("xbhdl_Data.xlsx")  # 处理文件的基础类

# sheet_names = xlsx.sheet_names # 读取excel文件中的sheet
#pd.read_excel('path_to_file.xls', sheet_name=None)
# 显示数据框内容
# print(df)
# print(sheet_names)

df = pd.read_excel('2024.06.24-06.29.xlsx')
# xlsx = pd.ExcelFile("xbhdl_Data_bk.xlsx")  # 处理文件的基础类
# copy_and_rename(source_file, destination_folder, new_filename)
# 设置循环插入的数组
allList = [[],[],[],[],[],[]]
for row_index, row in df.iterrows():
    #if row_index == 0 or row_index == 1 : #row.iloc[2] 
    #    continue
    #else :
    allList[0].insert(row_index, row.iloc[1])
    allList[1].insert(row_index, row.iloc[2])
    allList[2].insert(row_index, row.iloc[3])
    allList[3].insert(row_index, row.iloc[4])
    allList[4].insert(row_index, row.iloc[5])
    allList[5].insert(row_index, row.iloc[6])

    #allList[0][0] = row.iloc[1]
    #allList[1][0] = row.iloc[2]
    #allList[2][0] = row.iloc[3]
    #allList[3][0] = row.iloc[4]
    #allList[4][0] = row.iloc[5]
    #allList[5][0] = row.iloc[6]
    # print(row_index,row.iloc[1])
# print(allList)
for lists in allList:
    print(lists)
    copy_and_rename('template.xlsx', 'generateData\\', str(lists[0].date()) + '.xlsx')
    # 最终目录
    dist = 'generateData\\'+ str(lists[0].date()) + '.xlsx'
    # df = pd.read_excel(dist)
    # xlsx = pd.ExcelFile("xbhdl_Data.xlsx")  # 处理文件的基础类
    # sheet_names = xlsx.sheet_names # 读取excel文件中的sheet

    # 加载已存在的Excel文件
    workbook = load_workbook(dist)
    # 选择要插入数据的工作表
    sheet = workbook.active
    # 处理数据
    special = lists[1].split('\n', 1)
    cooked = lists[2].split('/', 1)
    brine = lists[11].split('/', 1)
    print(cooked)
    sheet.cell(row=3, column=2).value = lists[0].date() # 日期
    sheet.cell(row=3, column=3).value = special[0] # 特色菜
    sheet.cell(row=3, column=4).value = "200g"
    sheet.cell(row=3, column=5).value = lists[0].date()
    sheet.cell(row=3, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=4, column=2).value = lists[0].date() # 日期
    sheet.cell(row=4, column=3).value = cooked[0] # 
    sheet.cell(row=4, column=4).value = "200g"
    sheet.cell(row=4, column=5).value = lists[0].date()
    sheet.cell(row=4, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=5, column=2).value = lists[0].date() # 日期
    sheet.cell(row=5, column=3).value = cooked[1] # 
    sheet.cell(row=5, column=4).value = "200g"
    sheet.cell(row=5, column=5).value = lists[0].date()
    sheet.cell(row=5, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=6, column=2).value = lists[0].date() # 日期
    sheet.cell(row=6, column=3).value = lists[3] # 
    sheet.cell(row=6, column=4).value = "200g"
    sheet.cell(row=6, column=5).value = lists[0].date()
    sheet.cell(row=6, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=7, column=2).value = lists[0].date() # 日期
    sheet.cell(row=7, column=3).value = lists[5] # 
    sheet.cell(row=7, column=4).value = "200g"
    sheet.cell(row=7, column=5).value = lists[0].date()
    sheet.cell(row=7, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=8, column=2).value = lists[0].date() # 日期
    sheet.cell(row=8, column=3).value = lists[6] # 
    sheet.cell(row=8, column=4).value = "200g"
    sheet.cell(row=8, column=5).value = lists[0].date()
    sheet.cell(row=8, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=9, column=2).value = lists[0].date() # 日期
    sheet.cell(row=9, column=3).value = lists[7] # 
    sheet.cell(row=9, column=4).value = "200g"
    sheet.cell(row=9, column=5).value = lists[0].date()
    sheet.cell(row=9, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=10, column=2).value = lists[0].date() # 日期
    sheet.cell(row=10, column=3).value = lists[8] # 
    sheet.cell(row=10, column=4).value = "200g"
    sheet.cell(row=10, column=5).value = lists[0].date()
    sheet.cell(row=10, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=11, column=2).value = lists[0].date() # 日期
    sheet.cell(row=11, column=3).value = lists[9] # 
    sheet.cell(row=11, column=4).value = "200g"
    sheet.cell(row=11, column=5).value = lists[0].date()
    sheet.cell(row=11, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=12, column=2).value = lists[0].date() # 日期
    sheet.cell(row=12, column=3).value = lists[10] # 
    sheet.cell(row=12, column=4).value = "200g"
    sheet.cell(row=12, column=5).value = lists[0].date()
    sheet.cell(row=12, column=6).value = lists[0].date()+ timedelta(hours=48)
    #brine
    sheet.cell(row=13, column=2).value = lists[0].date() # 日期
    sheet.cell(row=13, column=3).value = brine[0] # 
    sheet.cell(row=13, column=4).value = "200g"
    sheet.cell(row=13, column=5).value = lists[0].date()
    sheet.cell(row=13, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=14, column=2).value = lists[0].date() # 日期
    sheet.cell(row=14, column=3).value = brine[1] # 
    sheet.cell(row=14, column=4).value = "200g"
    sheet.cell(row=14, column=5).value = lists[0].date()
    sheet.cell(row=14, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=15, column=2).value = lists[0].date() # 日期
    sheet.cell(row=15, column=3).value = lists[12] # 
    sheet.cell(row=15, column=4).value = "200g"
    sheet.cell(row=15, column=5).value = lists[0].date()
    sheet.cell(row=15, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=16, column=2).value = lists[0].date() # 日期
    sheet.cell(row=16, column=3).value = lists[13] # 
    sheet.cell(row=16, column=4).value = "200g"
    sheet.cell(row=16, column=5).value = lists[0].date()
    sheet.cell(row=16, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=17, column=2).value = lists[0].date() # 日期
    sheet.cell(row=17, column=3).value = lists[15] # 
    sheet.cell(row=17, column=4).value = "200g"
    sheet.cell(row=17, column=5).value = lists[0].date()
    sheet.cell(row=17, column=6).value = lists[0].date()+ timedelta(hours=48)
    
    sheet.cell(row=18, column=2).value = lists[0].date() # 日期
    sheet.cell(row=18, column=3).value = lists[16] # 
    sheet.cell(row=18, column=4).value = "200g"
    sheet.cell(row=18, column=5).value = lists[0].date()
    sheet.cell(row=18, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=19, column=2).value = lists[0].date() # 日期
    sheet.cell(row=19, column=3).value = lists[17] # 
    sheet.cell(row=19, column=4).value = "200g"
    sheet.cell(row=19, column=5).value = lists[0].date()
    sheet.cell(row=19, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=20, column=2).value = lists[0].date() # 日期
    sheet.cell(row=20, column=3).value = lists[18] # 
    sheet.cell(row=20, column=4).value = "200g"
    sheet.cell(row=20, column=5).value = lists[0].date()
    sheet.cell(row=20, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=21, column=2).value = lists[0].date() # 日期
    sheet.cell(row=21, column=3).value = lists[20] # 
    sheet.cell(row=21, column=4).value = "200g"
    sheet.cell(row=21, column=5).value = lists[0].date()
    sheet.cell(row=21, column=6).value = lists[0].date()+ timedelta(hours=48)

    sheet.cell(row=22, column=2).value = lists[0].date() # 日期
    sheet.cell(row=22, column=3).value = lists[21] # 
    sheet.cell(row=22, column=4).value = "200g"
    sheet.cell(row=22, column=5).value = lists[0].date()
    sheet.cell(row=22, column=6).value = lists[0].date()+ timedelta(hours=48)

    # 执行第二张要填充的表格
    # 选择要插入数据的工作表
    sheet_two = workbook['label']
    sheet_two.cell(row=3, column=2).value = special[0]
    sheet_two.cell(row=4, column=2).value = lists[0].date()

    sheet_two.cell(row=3, column=6).value = cooked[0]
    sheet_two.cell(row=4, column=6).value = lists[0].date()

    sheet_two.cell(row=3, column=10).value = cooked[1]
    sheet_two.cell(row=4, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=9, column=2).value = lists[3]
    sheet_two.cell(row=10, column=2).value = lists[0].date()

    sheet_two.cell(row=9, column=6).value = lists[5]
    sheet_two.cell(row=10, column=6).value = lists[0].date()

    sheet_two.cell(row=9, column=10).value = lists[6]
    sheet_two.cell(row=10, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=15, column=2).value = lists[7]
    sheet_two.cell(row=16, column=2).value = lists[0].date()

    sheet_two.cell(row=15, column=6).value = lists[8]
    sheet_two.cell(row=16, column=6).value = lists[0].date()

    sheet_two.cell(row=15, column=10).value = lists[9]
    sheet_two.cell(row=16, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=21, column=2).value = lists[10]
    sheet_two.cell(row=22, column=2).value = lists[0].date()

    sheet_two.cell(row=21, column=6).value = brine[0]
    sheet_two.cell(row=22, column=6).value = lists[0].date()

    sheet_two.cell(row=21, column=10).value = brine[1]
    sheet_two.cell(row=22, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=27, column=2).value = lists[12]
    sheet_two.cell(row=28, column=2).value = lists[0].date()

    sheet_two.cell(row=27, column=6).value = lists[13]
    sheet_two.cell(row=28, column=6).value = lists[0].date()

    sheet_two.cell(row=27, column=10).value = lists[15]
    sheet_two.cell(row=28, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=33, column=2).value = lists[16]
    sheet_two.cell(row=34, column=2).value = lists[0].date()

    sheet_two.cell(row=33, column=6).value = lists[17]
    sheet_two.cell(row=34, column=6).value = lists[0].date()

    sheet_two.cell(row=33, column=10).value = lists[18]
    sheet_two.cell(row=34, column=10).value = lists[0].date()
    ##
    sheet_two.cell(row=39, column=6).value = lists[20]
    sheet_two.cell(row=40, column=6).value = lists[0].date()

    sheet_two.cell(row=39, column=10).value = lists[21]
    sheet_two.cell(row=40, column=10).value = lists[0].date()

    # 保存修改后的文件
    workbook.save(dist)